from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status
import json
import pymysql  # 以MySQL为例，其他数据库可替换对应驱动（如psycopg2 for PostgreSQL）
from pymysql.cursors import DictCursor  # 使查询结果以字典形式返回


class SQLExecutorView(APIView):
    """
    接收SQL语句和数据库连接信息，执行SQL并返回结果
    支持查询类SQL（SELECT）和非查询类SQL（INSERT/UPDATE/DELETE等）
    """

    def post(self, request):
        # 1. 获取请求参数并验证
        required_params = ['sql', 'connection_info']
        for param in required_params:
            if param not in request.data:
                return Response(
                    {"error": f"缺少必要参数: {param}"},
                    status=status.HTTP_400_BAD_REQUEST
                )

        sql = request.data.get('sql', '').strip()
        connection_info = request.data.get('connection_info', {})

        # 验证connection_info必填字段
        conn_required = ['host', 'user', 'password', 'database']
        for conn_param in conn_required:
            if conn_param not in connection_info or not connection_info[conn_param]:
                return Response(
                    {"error": f"数据库连接信息缺少必要字段: {conn_param}"},
                    status=status.HTTP_400_BAD_REQUEST
                )

        # 2. 提取数据库连接参数
        db_config = {
            'host': connection_info['host'],
            'user': connection_info['user'],
            'password': connection_info['password'],
            'database': connection_info['database'],
            'port': connection_info.get('port', 3306),  # 默认为MySQL端口
            'charset': connection_info.get('charset', 'utf8mb4'),
            'cursorclass': DictCursor  # 结果以字典形式返回（字段名作为key）
        }

        connection = None
        try:
            # 3. 建立数据库连接
            connection = pymysql.connect(**db_config)
            cursor = connection.cursor()

            # 4. 执行SQL
            affected_rows = cursor.execute(sql)  # 执行SQL，返回受影响的行数

            # 5. 处理执行结果
            if sql.strip().upper().startswith('SELECT'):
                # 查询类SQL：返回查询结果
                data = cursor.fetchall()  # 获取所有查询结果
                result = {
                    "status": "success",
                    "message": f"查询成功，返回 {len(data)} 条记录",
                    "data": data,
                    "affected_rows": affected_rows
                }
            else:
                # 非查询类SQL（如INSERT/UPDATE/DELETE）：提交事务并返回受影响行数
                connection.commit()
                result = {
                    "status": "success",
                    "message": f"SQL执行成功",
                    "affected_rows": affected_rows,
                    "data": None
                }

            return Response(result, status=status.HTTP_200_OK)

        except pymysql.MySQLError as e:
            # 数据库错误（如SQL语法错误、权限不足等）
            if connection:
                connection.rollback()  # 执行失败回滚事务
            return Response(
                {
                    "status": "error",
                    "message": f"数据库执行错误: {str(e)}",
                    "error_code": e.args[0],
                    "error_detail": e.args[1]
                },
                status=status.HTTP_500_INTERNAL_SERVER_ERROR
            )
        except Exception as e:
            # 其他未知错误
            return Response(
                {
                    "status": "error",
                    "message": f"执行失败: {str(e)}"
                },
                status=status.HTTP_500_INTERNAL_SERVER_ERROR
            )
        finally:
            # 确保连接关闭
            if connection:
                connection.close()



